package com.springboot.film.dao;

import com.springboot.film.entity.films;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component(value = "filmsDao")
public interface filmsDao {

    //电影信息：films

    /**
     * 显示总数量
     * @return
     */
    @Select("select count(*) from films")
    int getCount();

    /**
     * 全部分页显示
     * @return
     */
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp," +
            "FROM_UNIXTIME(time, '%Y-%m-%d') as time_temp" +
            " from films ORDER BY hot DESC limit #{pos},#{size}")
    List<films> findByPage(@Param("pos")int pos, @Param("size")int size);

    /**
     * 显示搜索的总数量
     * @return
     */
    @Select("select count(*) from films where ${key} like concat(concat('%',#{name}),'%')")
    int getsearchCount(@Param("key") String key, @Param("name")String name);

    /**
     * 搜索并分页显示
     * @return
     */
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp," +
            "FROM_UNIXTIME(time, '%Y-%m-%d') as time_temp" +
            " from films where ${key} like concat(concat('%',#{name}),'%') limit #{pos},#{size}")
    List<films> findsearchByPage(@Param("key")String key,@Param("name")String name,@Param("pos")int pos, @Param("size")int size);

    /**
     * 新增
     */
    @Insert("INSERT INTO films(name,content,img,vedio,type,vip_look,hot,time) " +
            "VALUES(#{name},#{content},#{img},#{vedio},#{type},#{vip_look},#{hot},UNIX_TIMESTAMP(#{time_temp}))")
    void add(films films);

    /**
     * 删除
     */
    @Delete("DELETE FROM films WHERE ID=#{id}")
    void delete(@Param("id")int id);

    /**
     * 修改(先找到并显示，再修改)
     */
    @Select("select *,FROM_UNIXTIME(time, '%Y-%m-%d') as time_temp from films where id=#{id}")
    List<films> getUserone(@Param("id")int id);

    @Update("update films set name=#{name},content=#{content},img=#{img},vedio=#{vedio}," +
            "type=#{type},vip_look=#{vip_look},hot=#{hot},time=UNIX_TIMESTAMP(#{time_temp}) where id=#{id}")
    void update(films films);

    /**
     * 前台操作
     */
    //按照热度降序排列
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp," +
            "FROM_UNIXTIME(time, '%Y-%m-%d') as time_temp from films ORDER BY hot DESC")
    List<films> getallhotfilms();
    //按照时间降序排列
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp," +
            "FROM_UNIXTIME(time, '%Y-%m-%d') as time_temp from films ORDER BY time DESC")
    List<films> getalltimefilms();
    /**
     * 显示搜索的总数量
     * @return
     */
    @Select("select count(*) from films where type=#{type}")
    int gettypeCount(@Param("type") String type);
    @Select("select count(*) from films where vip_look=#{vip_look}")
    int getviplookCount(@Param("vip_look") int vip_look);
    @Select("select count(*) from films where type=#{type} and vip_look=#{vip_look}")
    int getallCount(@Param("type") String type,@Param("vip_look") int vip_look);
    @Select("select count(*) from films where name like concat(concat('%',#{keyword}),'%') " +
            "or content like concat(concat('%',#{keyword}),'%')")
    int getallsearchCount(@Param("keyword") String keyword);
    /**
     * 搜索并分页显示
     * @return
     */
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp" +
            " from films where type=#{type} ORDER BY hot DESC limit #{pos},#{size}")
    List<films> findtypeByPage(@Param("type")String type,@Param("pos")int pos, @Param("size")int size);
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp" +
            " from films where vip_look=#{vip_look} ORDER BY hot DESC limit #{pos},#{size}")
    List<films> findviplookByPage(@Param("vip_look")int vip_look,@Param("pos")int pos, @Param("size")int size);
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp" +
            " from films where type=#{type} and vip_look=#{vip_look} ORDER BY hot DESC limit #{pos},#{size}")
    List<films> findallByPage(@Param("type")String type,@Param("vip_look")int vip_look,@Param("pos")int pos, @Param("size")int size);
    @Select("select a.*,(case a.vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp " +
            "from films a,userinfo b where b.id=#{id} and b.fid#{fid}=a.id")
    List<films> getmyfilms(@Param("id")int id,@Param("fid")int fid);
    @Select("select *,(case vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp" +
            " from films where name like concat(concat('%',#{keyword}),'%') " +
            "or content like concat(concat('%',#{keyword}),'%') ORDER BY hot DESC limit #{pos},#{size}")
    List<films> getallsearch(@Param("keyword") String keyword,@Param("pos")int pos, @Param("size")int size);

    /**
     * 个性推荐算法
     * @return
     */
    @Select("select type from films where id=#{id}")
    String gettype(@Param("id")int id);
    @Select("select content from films where id=#{id}")
    String getcontent(@Param("id")int id);
    @Select("select count(*) from films where type=#{type}")
    int getmaxid(@Param("type")String type);
    @Select("select id from films where type=#{type} limit #{pos},1;")
    int getid(@Param("type")String type,@Param("pos")int pos);
    @Select("select a.*,(case a.vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp " +
            "from films a where a.id=#{id}")
    List<films> getmylike(@Param("id")int id);

    @Update("update films set hot=hot+1 where id=#{id}")
    void updatehot(@Param("id")int id);

    @Select("select a.*,b.name as uname,c.name as fname,round(a.time) as time_temp,c.*," +
            "(case c.vip_look when 0 then '免费观看' else '会员专享' end) as vip_look_temp " +
            "from looktime a,userinfo b,films c where a.uid=b.id and a.fid=c.id and a.uid=#{uid} order by time_temp desc;")
    List<films> getmyfilmsall(@Param("uid")int uid);

}
